﻿using AutoMapper;
using DFGK_WMS.Context;
using DFGK_WMS.IRepository;
using DFGK_WMS.Model.Enum;
using DFGK_WMS.Model.Models;
using Microsoft.EntityFrameworkCore;
using NetTaste;
using NPOI.SS.Formula.Functions;
using System.Linq.Expressions;

namespace DFGK_WMS.Repository
{
    public class TShippingLockCellRepository : BaseRepository<TShippingLockCell>, ITShippingLockCellRepository
    {
        public TShippingLockCellRepository(DFGK_DB Context, IMapper mapper) : base(Context, mapper)
        {
        }

        public bool LockCell(string materialCode, decimal expectedQTY, out List<TShippingLockCell> lockCellList, out string errorMsg, int? ownerId = null, string? BatchNo = null, int? supplierId = null)
        {
            errorMsg = "";
            lockCellList = new List<TShippingLockCell>();
            try
            {
                //查询出库的物料
                var material = Context.TMaterial.FirstOrDefault(t => t.Code == materialCode);
                if (material == null)
                {
                    errorMsg = "物料数据不存在";
                    return false;
                }
                decimal? sum = Context.TInventories.Where(s => s.MaterialCode == materialCode).Sum(t => t.AvailableQty);
                var cellQuery = Context.TCells;
                var shelfQuery = Context.TShelves;
                var routewayQuery = Context.TRouteways;
                var areaQuery = Context.TAreas;
                var whHouseQuery = Context.TWarehouses;

                var TInventoryQuery = (from inv in Context.TInventories
                                       join cell in cellQuery
                                       on inv.LocationCode equals cell.Code
                                       join slf in shelfQuery
                                       on cell.ShelfCode equals slf.Code
                                       join rtw in routewayQuery
                                       on cell.RoutewayId equals rtw.Id
                                       join area in areaQuery
                                       on slf.AreaCode equals area.Code
                                       join whHouse in whHouseQuery
                                       on area.WhCode equals whHouse.Code
                                       where
                                       cell.Status == 1 &&
                                       slf.Status == 1 &&
                                       rtw.Status == 1 &&
                                       area.Status == 1 &&
                                       whHouse.Status == 1
                                       select inv
                                      ).ToList();

                var invList = TInventoryQuery.Where(t => t.MaterialCode == materialCode && t.Status == 1 && t.IsLock == false).ToList();
                var tpMsg = string.Empty;
                var disTuoPan = new List<string>();//被禁用的托盘
                invList = invList.Where(t => GetTuoPanState(t.PalletBarcode, out tpMsg, out disTuoPan)).ToList();
                if (invList == null)
                {
                    errorMsg = "该物料所在托盘都已被禁用,托盘条码：" + string.Join(',', disTuoPan);
                    if (!string.IsNullOrEmpty(tpMsg)) errorMsg = tpMsg;
                    return false;
                }

                if (!string.IsNullOrEmpty(BatchNo))
                {
                    invList = invList.Where(t => t.BatchNo == BatchNo).ToList();
                }
                if (ownerId > 0)
                {
                    invList = invList.Where(t => t.OwnerId == ownerId).ToList();
                }
                if (supplierId > 0)
                {
                    invList = invList.Where(t => t.SupplierId == supplierId).ToList();
                }
                List<int?> status = new List<int?>
                {
                    (int)EmBillStatus.Create,
                    (int)EmBillStatus.Submit,
                    (int)EmBillStatus.Pass,
                };
                //一键出库优先级最高
                var shippingOrderDetailQuery = Context.TShippingOrderDetails.OrderBy(t => t.CreateDate);
                var slcOdQuery = (from lockCell in Context.TShippingLockCells
                                  join od in shippingOrderDetailQuery
                                  on lockCell.OrderDetailId equals od.Id
                                  select new { lockCell, od }
                                ).ToList();
                var slcOdQueryTest = slcOdQuery.ToList();
                List<TShippingLockCell> lockCells = slcOdQuery.Select(x => x.lockCell).ToList();
                List<TShippingOrderDetail> orderDetails = slcOdQuery.Select(x => x.od).ToList();


                orderDetails = orderDetails.Where(od => od.MaterialCode == materialCode && status.Contains(od.Status)).ToList();
                lockCells = lockCells.Where(loc => loc.MaterialCode == materialCode).ToList();
                slcOdQuery = (from lockCell in lockCells
                              join od in orderDetails
                              on lockCell.OrderDetailId equals od.Id
                              select new { lockCell, od }
                        ).ToList();


                //剔除被锁定的库存
                var lockInvIdQuery = (from lockCell in lockCells
                                      join od in orderDetails
                                      on lockCell.OrderDetailId equals od.Id
                                      where lockCell.IsPicking == true
                                      select lockCell.InventoryId
                            ).Distinct().ToList();
                int lockInvCount = lockInvIdQuery.Count();
                if (lockInvCount > 0)
                {
                    invList = invList.Where(t => !lockInvIdQuery.ToList().Contains(t.Id)).ToList();
                }
                //查出所有匹配的库存  出库优先级规则
                List<TInventory> TInventoryList = invList.OrderBy(x => ExtractDateFromCode(x.SmallBatchNo))
                                                        .ThenBy(x => x.LocationDepth)
                                                        .ThenBy(x => x.AvailableQty)
                                                        .ThenBy(x => x.LocationLineNo)
                                                        .ThenBy(x => x.LocationLayerNo)
                                                        .ThenBy(x => x.LocationColumnNo)
                                                        .ThenBy(x => x.InStockShortDate)
                                                        .ToList();
                //库存可用数量
                decimal totalTInventoryCount = TInventoryList.Sum(t => t.AvailableQty.GetValueOrDefault());
                if (expectedQTY > totalTInventoryCount)
                {
                    errorMsg = material.Name + "库存数量不足" + expectedQTY.ToString() + ",库存还剩：" + sum.GetValueOrDefault();
                    if (sum.GetValueOrDefault() >= expectedQTY)
                    {
                        errorMsg = material.Name + "可出数量不足" + expectedQTY.ToString() + ",部分库存可能已被其它出库单锁定,或者巷道货架仓位库区库房已被禁用" + ",目前可出数量：" + (int)totalTInventoryCount;
                    }
                    return false;
                }

                List<TInventory> allotTInventoryList = new List<TInventory>();
                List<TInventory> unAllotTInventoryList = new List<TInventory>();
                TInventory lastAllotTInventory = null;
                decimal qty = 0;

                //TInventoryList = TInventoryList.OrderBy(t => t.LocationLineNo).ThenBy(t => t.LocationLayerNo).ThenBy(t => t.LocationColumnNo).ThenBy(t => t.LocationDepth)
                //    .ThenBy(t => t.InStockDate).ThenByDescending(t => t.AvailableQty).ThenBy(t => t.LocationDepth).ToList();

                var groupInvs = TInventoryList.GroupBy(t => new { t.LocationLineNo, t.LocationLayerNo, t.LocationColumnNo });
                foreach (var group in groupInvs)
                {
                    List<TInventory> list = group.OrderBy(t => t.LocationDepth).ToList();
                    FilterInv(list, expectedQTY, ref qty, ref allotTInventoryList, ref unAllotTInventoryList);
                    if (qty >= expectedQTY)
                    {
                        break;
                    }
                }

                if (qty < expectedQTY && unAllotTInventoryList.Count > 0)
                {
                    lastAllotTInventory = unAllotTInventoryList.Where(t => qty + t.AvailableQty.GetValueOrDefault() == expectedQTY).OrderBy(t => t.LocationDepth).ThenBy(t => t.InStockShortDate).FirstOrDefault();
                    if (lastAllotTInventory == null)
                    {
                        List<string> allotColumns = allotTInventoryList != null && allotTInventoryList.Count > 0 ? allotTInventoryList.Select(t => t.LocationLineNo.ToString() + "_" + t.LocationLayerNo.ToString() + "_" + t.LocationColumnNo.ToString()).Distinct().ToList() : new List<string>();
                        lastAllotTInventory = unAllotTInventoryList.Where(t => !allotColumns.Contains(t.LocationLineNo.ToString() + "_" + t.LocationLayerNo.ToString() + "_" + t.LocationColumnNo.ToString())).OrderBy(t => t.LocationDepth).ThenBy(t => t.InStockShortDate).FirstOrDefault();
                        if (lastAllotTInventory == null)
                        {
                            lastAllotTInventory = unAllotTInventoryList.OrderBy(t => t.LocationDepth).ThenBy(t => t.InStockShortDate).FirstOrDefault();
                        }
                    }
                    else
                    {
                        allotTInventoryList.Add(lastAllotTInventory);
                        qty += lastAllotTInventory.AvailableQty.GetValueOrDefault();
                    }
                }

                if (qty + (lastAllotTInventory == null ? 0 : lastAllotTInventory.AvailableQty.GetValueOrDefault()) < expectedQTY)
                {
                    errorMsg = "可锁定的库存数量不足" + expectedQTY.ToString() + "，可能存在浅位库存在做其它业务";
                    return false;
                }
                int index = 0;
                if (allotTInventoryList != null && allotTInventoryList.Count > 0)
                {
                    foreach (var TInventory in allotTInventoryList)
                    {
                        index++;
                        lockCellList.Add(new TShippingLockCell
                        {
                            CellId = TInventory.LocationId,
                            CellCode = TInventory.LocationCode,
                            CellName = TInventory.LocationName,
                            CellDepthIndex = TInventory.LocationDepth,
                            BatchNo = TInventory.BatchNo,
                            SmallBatchNo = TInventory.SmallBatchNo,
                            InventoryId = TInventory.Id,
                            InventoryQty = TInventory.AvailableQty,
                            LockIndex = index,
                            MaterialCode = TInventory.MaterialCode,
                            MaterialId = TInventory.MaterialId,
                            MaterialName = TInventory.MaterialName,
                            MaterialKeyId = TInventory.MaterialKeyId,
                            OutQty = TInventory.AvailableQty,
                            PalletBarcode = TInventory.PalletBarcode,
                            IsPicking = false,
                            StackingStorageId = TInventory.StackingStorageId,
                            WhId = TInventory.WarehouseId,
                            RoutewayId = TInventory.RoutewayId,
                            CreateDate = TInventory.CreateDate,
                        });
                    }
                }

                if (lastAllotTInventory != null)
                {
                    decimal lastQty = expectedQTY - qty;
                    if (lastQty > 0)
                    {
                        index++;
                        lockCellList.Add(new TShippingLockCell
                        {
                            CellId = lastAllotTInventory.LocationId,
                            CellCode = lastAllotTInventory.LocationCode,
                            CellName = lastAllotTInventory.LocationName,
                            CellDepthIndex = lastAllotTInventory.LocationDepth,
                            BatchNo = lastAllotTInventory.BatchNo,
                            SmallBatchNo = lastAllotTInventory.SmallBatchNo,
                            InventoryId = lastAllotTInventory.Id,
                            InventoryQty = lastAllotTInventory.AvailableQty,
                            LockIndex = index,
                            MaterialCode = lastAllotTInventory.MaterialCode,
                            MaterialId = lastAllotTInventory.MaterialId,
                            MaterialName = lastAllotTInventory.MaterialName,
                            MaterialKeyId = lastAllotTInventory.MaterialKeyId,
                            OutQty = lastQty,
                            PalletBarcode = lastAllotTInventory.PalletBarcode,
                            IsPicking = lastQty < lastAllotTInventory.AvailableQty.GetValueOrDefault(),
                            StackingStorageId = lastAllotTInventory.StackingStorageId,
                            WhId = lastAllotTInventory.WarehouseId,
                            RoutewayId = lastAllotTInventory.RoutewayId,
                            CreateDate = lastAllotTInventory.CreateDate,
                        });
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                errorMsg = "未知异常:" + ex.Message;
                return false;
            }
        }
        private void FilterInv(List<TInventory> list, decimal expectedQTY, ref decimal qty, ref List<TInventory> allotInventoryList, ref List<TInventory> unAllotInventoryList)
        {
            //先找有没有正好数量的库存
            //foreach (var inventory in list)
            //{
            //    if (inventory.AvailableQty == expectedQTY)
            //    {
            //        allotInventoryList.Add(inventory);
            //        qty += inventory.AvailableQty.GetValueOrDefault();
            //        break;
            //    }
            //}
            //if (allotInventoryList == null || allotInventoryList.Count() == 0)
            //{
            foreach (var inventory in list)
            {
                if (qty == expectedQTY)
                {
                    break;
                }
                if (qty + inventory.AvailableQty.GetValueOrDefault() == expectedQTY)
                {
                    allotInventoryList.Add(inventory);
                    qty += inventory.AvailableQty.GetValueOrDefault();
                    break;
                }
                //数量多的
                else if (qty + inventory.AvailableQty.GetValueOrDefault() > expectedQTY)
                {
                    unAllotInventoryList.Add(inventory);
                    break;
                }
                else
                {
                    allotInventoryList.Add(inventory);
                    qty += inventory.AvailableQty.GetValueOrDefault();
                }
            }
            //}
        }
        /// <summary>
        /// 批次号拆解
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public int ExtractDateFromCode(string bacNo)
        {
            if (string.IsNullOrEmpty(bacNo))
            {
                return 0;
            }
            if (bacNo.Length < 12)
            {
                return 0;
            }
            string time = bacNo.Substring(2, 8);
            bool containsLetter = time.Any(char.IsLetter);
            if (containsLetter)
            {
                return 0;
            }
            return Convert.ToInt32(time);
        }
        private bool GetTuoPanState(string palletBarcode, out string errorMsg, out List<string> disTuoPan)
        {
            errorMsg = string.Empty;
            disTuoPan = new List<string>();
            var tuoPan = Context.TPallets.FirstOrDefault(s => s.PalletBarcode == palletBarcode);
            if (tuoPan != null)
            {
                if (tuoPan.Status == 0)
                {
                    disTuoPan.Add(tuoPan.PalletBarcode);
                }
                return tuoPan.Status == 1 ? true : false;
            }
            errorMsg = "托盘不存在";
            return false;
        }
        /// <summary>
        /// 获取可出数量
        /// </summary>
        public decimal GetKeChuInvCount(string materialCode)
        {
            //查询出库的物料
            var material = Context.TMaterial.FirstOrDefault(t => t.Code == materialCode);
            if (material == null)
            {
                return 0;
            }
            decimal? sum = Context.TInventories.Where(s => s.MaterialCode == materialCode).Sum(t => t.AvailableQty);
            var cellQuery = Context.TCells;
            var shelfQuery = Context.TShelves;
            var routewayQuery = Context.TRouteways;
            var areaQuery = Context.TAreas;
            var whHouseQuery = Context.TWarehouses;

            var TInventoryQuery = (from inv in Context.TInventories
                                    join cell in cellQuery
                                    on inv.LocationCode equals cell.Code
                                    join slf in shelfQuery
                                    on cell.ShelfCode equals slf.Code
                                    join rtw in routewayQuery
                                    on cell.RoutewayId equals rtw.Id
                                    join area in areaQuery
                                    on slf.AreaCode equals area.Code
                                    join whHouse in whHouseQuery
                                    on area.WhCode equals whHouse.Code
                                    where
                                    cell.Status == 1 &&
                                    slf.Status == 1 &&
                                    rtw.Status == 1 &&
                                    area.Status == 1 &&
                                    whHouse.Status == 1
                                    select inv
                                    ).ToList();

            var invList = TInventoryQuery.Where(t => t.MaterialCode == materialCode && t.Status == 1 && t.IsLock == false && t.WhetherQualified == "合格").ToList();
            var tpMsg = string.Empty;
            var disTuoPan = new List<string>();//被禁用的托盘
            invList = invList.Where(t => GetTuoPanState(t.PalletBarcode, out tpMsg, out disTuoPan)).ToList();
            //如果改物料的托盘都已被禁用则返回0
            if (invList == null)
            {
                return 0;
            }
            List<int?> status = new List<int?>
            {
                (int)EmBillStatus.Create,
                (int)EmBillStatus.Submit,
                (int)EmBillStatus.Pass,
            };
            //一键出库优先级最高
            var shippingOrderDetailQuery = Context.TShippingOrderDetails.OrderBy(t => t.CreateDate);
            var slcOdQuery = (from lockCell in Context.TShippingLockCells
                                join od in shippingOrderDetailQuery
                                on lockCell.OrderDetailId equals od.Id
                                select new { lockCell, od }
                            ).ToList();
            var slcOdQueryTest = slcOdQuery.ToList();
            List<TShippingLockCell> lockCells = slcOdQuery.Select(x => x.lockCell).ToList();
            List<TShippingOrderDetail> orderDetails = slcOdQuery.Select(x => x.od).ToList();


            orderDetails = orderDetails.Where(od => od.MaterialCode == materialCode && status.Contains(od.Status)).ToList();
            lockCells = lockCells.Where(loc => loc.MaterialCode == materialCode).ToList();
            slcOdQuery = (from lockCell in lockCells
                            join od in orderDetails
                            on lockCell.OrderDetailId equals od.Id
                            select new { lockCell, od }
                    ).ToList();


            //剔除被锁定的库存
            var lockInvIdQuery = (from lockCell in lockCells
                                    join od in orderDetails
                                    on lockCell.OrderDetailId equals od.Id
                                    where lockCell.IsPicking == true
                                    select lockCell.InventoryId
                        ).Distinct().ToList();
            int lockInvCount = lockInvIdQuery.Count();
            if (lockInvCount > 0)
            {
                invList = invList.Where(t => !lockInvIdQuery.ToList().Contains(t.Id)).ToList();
            }
            //查出所有匹配的库存  出库优先级规则
            List<TInventory> TInventoryList = invList.OrderBy(x => ExtractDateFromCode(x.SmallBatchNo))
                                                    .ThenBy(x => x.LocationDepth)
                                                    .ThenBy(x => x.AvailableQty)
                                                    .ThenBy(x => x.LocationLineNo)
                                                    .ThenBy(x => x.LocationLayerNo)
                                                    .ThenBy(x => x.LocationColumnNo)
                                                    .ThenBy(x => x.InStockShortDate)
                                                    .ToList();
            //库存可用数量
            return TInventoryList.Sum(t => t.AvailableQty.GetValueOrDefault());
        }

        public decimal GetInvCount(string materialCode)
        {
            return Context.TInventories.Where(s => s.MaterialCode == materialCode).Sum(t => t.AvailableQty).GetValueOrDefault();
        }
    }
}
